package edu.cmu.cs.webapp.ioms.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import edu.cmu.cs.webapp.ioms.dao.DataAccessException;
import edu.cmu.cs.webapp.ioms.dao.IngredientDAO;
import edu.cmu.cs.webapp.ioms.models.IOMSPackage;
import edu.cmu.cs.webapp.ioms.models.Ingredient;

public class IngredientDAOImpl extends DAO implements IngredientDAO{

	public IngredientDAOImpl() {
		// TODO Auto-generated constructor stub
		super();
	}

	public List<Ingredient> getAllIngr() throws DataAccessException{
		Connection con = null;
		Ingredient ing = null;
        List<Ingredient> inglst = new ArrayList<Ingredient>();
        try {
        	con = getConnection();
        	Statement stmt = con.createStatement();
        	String sql = "select * from ingredient";
        	ResultSet rs = stmt.executeQuery(sql);
        	while(rs.next()){
        		ing = IOMSPackage.eINSTANCE.getIOMSFactory().createIngredient();
        		
        		ing.setIng_id(rs.getInt("ing_id"));
        		ing.setName(rs.getString("name"));
        		ing.setInventory(rs.getDouble("inventory"));
        		ing.setUnit_cost(rs.getDouble("unit_cost"));
        		ing.setRemark(rs.getString("remark"));
        		ing.setLastUpdate(rs.getDate("lastupdate"));
        		ing.setUpdateBy(rs.getString("updateby"));

        		inglst.add(ing);
        	}
            stmt.close();
            con.close();    	
        } catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);
        }
        return inglst;
	}

	@Override
	public boolean chargeInventory(int ingId, double newInventory) throws DataAccessException{
		// TODO Auto-generated method stub
		Connection con = null;
		try {
			con = getConnection();
			
			String str="update ingredient set inventory=" + newInventory +
			" where ing_id=" + ingId;

            PreparedStatement pstmt = con.prepareStatement(str);
            pstmt.executeUpdate();
            System.out.println(str);
            		
			pstmt.close();
			con.close();
			
			log("Update inventory for '" + ingId + "'", "Update");
			
		} catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);
		}
		return true;
	}

	@Override
	public double findDemandLevel(int ingId) throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;
		double result = 0;

        try {
        	con = getConnection();
        	Statement stmt = con.createStatement();
        	String sql = "select sum(item.quantity * formula.ing_std_quantity) sum from  ( ( ( ingredient join formula using(ing_id)) join product using(prod_id) ) join item using(prod_id) ) join po using(order_id) where po.status='pending' and ingredient.ing_id=" + ingId;
        	ResultSet rs = stmt.executeQuery(sql);
        	if(rs.next())
        		result = rs.getDouble("sum");
        	
            stmt.close();
            con.close();    	
            
        } catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);
        }
        return result;
	}

	@Override
	public boolean addIng(Ingredient ing) throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;

		try {
			con = getConnection();
			
            PreparedStatement pstmt = con.prepareStatement(
            "insert into ingredient(name, inventory, unit_cost," +
            "remark, lastupdate, updateby) " +
            "values (? ,?, ?, ?, ?, ?)");
            
            pstmt.setString(1, ing.getName());
            pstmt.setDouble(2, ing.getInventory());
            pstmt.setDouble(3, ing.getUnit_cost());
            pstmt.setString(4, ing.getRemark());
            pstmt.setDate(5, (Date)ing.getLastUpdate());
            pstmt.setString(6, ing.getUpdateBy());
            
            pstmt.executeUpdate();
            System.out.println("insert: " + ing.toString());
			          		
			pstmt.close();
			con.close();
			
			log("Add new ingredient [" + ing.toString() + "]", "Add");
			
			return true;
		} catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);           
		}
	}

	@Override
	public boolean updateIng(Ingredient ing) throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;
		try {
			con = getConnection();
			
			String str="update ingredient set ";
			if( ing.getName()!= null )
				str += "name='" + ing.getName() + "',";
			if( ing.getInventory() != 0)
				str += "inventory=" + ing.getInventory() +",";
			if( ing.getUnit_cost() != 0)
				str += "unit_cost=" + ing.getUnit_cost() +",";
			if( ing.getRemark()!= null )
				str += "remark='" + ing.getRemark() + "',";
			if( ing.getLastUpdate()!= null )
				str += "lastupdate='" + ( new Date(ing.getLastUpdate().getTime())).toString() + "',";
			if( ing.getUpdateBy() != null )
				str += "updateby='" + ing.getUpdateBy() + "',";

			str = str.substring(0, str.lastIndexOf(","));
			str += " where ing_id=" + ing.getIng_id();	
			System.out.println(str);
            PreparedStatement pstmt = con.prepareStatement(str);
            pstmt.executeUpdate();
            System.out.println("update: " + ing.toString());
            		
			pstmt.close();
			con.close();
			
			log("Update ingredient [" + ing.toString() + "]", "Update");
			
		} catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);
		}
		return true;
	}

	@Override
	public Ingredient getIng(Ingredient ing) throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;

        try {
        	con = getConnection();
        	Statement stmt = con.createStatement();
        	String sql = "select * from ingredient where ing_id=" + ing.getIng_id();
        	ResultSet rs = stmt.executeQuery(sql);
        	if(rs.next()){       		
        		ing.setName(rs.getString("name"));
        		ing.setInventory(rs.getDouble("inventory"));
        		ing.setUnit_cost(rs.getDouble("unit_cost"));
        		ing.setRemark(rs.getString("remark"));
        		ing.setLastUpdate(rs.getDate("lastupdate"));
        		ing.setUpdateBy(rs.getString("updateby"));
        		return ing;
        	}
            stmt.close();
            con.close();    	
        } catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);
        }
        return null;
	}

	@Override
	public boolean deleteIng(Ingredient ing) throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;
		try {
			con = getConnection();
			PreparedStatement pstmt = con
					.prepareStatement("delete from ingredient where ing_id ="
							+ ing.getIng_id());
			pstmt.executeUpdate();
			System.out.println("delete: " + ing.toString());

			pstmt.close();
			con.close();
			
			log("Delete ingredient [" + ing.toString() + "]", "Delete");
		} catch (Exception e) {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e2) { /* ignore */
			}
			throw new DataAccessException(e);
		}
		return true;
	}


}
